package com.dilean.impl.dao;

import java.io.File;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.ListIterator;
import java.util.Map;

import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.dilean.api.dao.PersonDAO;
import com.dilean.api.model.Attribute;
import com.dilean.api.model.AttributeListValue;
import com.dilean.api.model.AttributeValue;
import com.dilean.api.model.AttributeValueFactory;
import com.dilean.api.model.Person;
import com.dilean.impl.model.PersonImpl;

@Repository
@Transactional(readOnly = true)
public class PersonDAOImpl extends AbstractDAOImpl<Person> implements PersonDAO
{
	private Log log = LogFactory.getLog(PersonDAOImpl.class);
	
    @Autowired
    private AttributeValueFactory attrValueFactory;
    
    @Override
    public Person findByIdWithFriends(Integer id)
    {
    	Person person = em.find(PersonImpl.class, id);
    	Hibernate.initialize(person.getFriendRequests());
    	Hibernate.initialize(person.getFriends());
        return person;
    }

	@Override
	public Person findByIdWithConversations(Integer id) 
	{
		Person person = em.find(PersonImpl.class, id);
    	Hibernate.initialize(person.getConversations());
        return person;
	}

	@Override
    @Transactional(readOnly = false)
    public Person save(Person person) 
	{
		Map<Attribute, AttributeValue<?>> updatedAttributes = person.getAttrsToUpdate();
		if (log.isDebugEnabled())
			log.debug("Save : " + person + ", UpdateParams : " + updatedAttributes);
		
	    for (Attribute attrToUpdate : updatedAttributes.keySet())
	    {
            AttributeValue<?> attrValue = updatedAttributes.get(attrToUpdate);

            final String sqlDelete = 
                    "delete from \n" + 
                    "   parameters\n" + 
                    "where\n" + 
                    "   pers_id = :personId and\n" + 
                    "   attr_id = :attrId";
                
			final Query queryDelete = em.createNativeQuery(sqlDelete);
			queryDelete.setParameter("personId", attrValue.getPersonId());
			queryDelete.setParameter("attrId", attrValue.getAttribute().getId());
			queryDelete.executeUpdate();

			final String sqlInsert =
	                "insert into \n" + 
	                "   parameters\n" + 
	                "values \n" + 
	                "   (:personId, \n" + 
	                "   :attrId, \n" + 
	                "   :listValueId, \n" + 
	                "   :value, \n" + 
	                "   :date, \n" + 
	                "   :data, \n" + 
	                "   :orderNumber)"; 
	                    		 	                
			Session session = em.unwrap(Session.class);
			SQLQuery queryInsert = session.createSQLQuery(sqlInsert);
			queryInsert.setInteger("personId", attrValue.getPersonId());
			queryInsert.setParameter("attrId", attrValue.getAttribute().getId());

			if (!attrToUpdate.isMultiple()) {
				Object o = attrValue.getValue();

				queryInsert.setBigInteger("listValueId", null);
				queryInsert.setString("value", null);
				queryInsert.setDate("date", null);
				queryInsert.setString("data", null);
				queryInsert.setInteger("orderNumber", 0);

				if (o instanceof Date)
					queryInsert.setParameter("date", o);
				else if (o instanceof File) 
					queryInsert.setParameter("value", ((File) o).getAbsolutePath());
				else if (o instanceof AttributeListValue) 
					queryInsert.setParameter("listValueId", ((AttributeListValue) o).getId());
				else
					queryInsert.setParameter("value", o.toString());

				queryInsert.executeUpdate();

			} else {
				// execute delete and then insert new new
				List<?> list = (List<?>) attrValue.getValue();
				ListIterator<?> i = list.listIterator();
				while (i.hasNext()) {
					Object o = i.next();
					int orderNumber = i.nextIndex() - 1;

					queryInsert.setBigInteger("listValueId", null);
					queryInsert.setString("value", null);
					queryInsert.setDate("date", null);
					queryInsert.setString("data", null);
					queryInsert.setInteger("orderNumber", orderNumber);

					if (o instanceof Date)
						queryInsert.setParameter("date", o);
					else if (o instanceof File)
						queryInsert.setParameter("value", ((File) o).getAbsolutePath());
					else if (o instanceof AttributeListValue)
						queryInsert.setParameter("listValueId", ((AttributeListValue) o).getId());
					else
						queryInsert.setParameter("value", o.toString());
					
					queryInsert.executeUpdate();
				}
			}
		}
		person.clearAttrsToUpdate();
		return em.merge(person);
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> AttributeValue<T> getParameter(Integer personId, Integer attrId) {
		final String sql =
            "select\n" + 
            "   list_value_id,\n" + 
            "   value,\n" + 
            "   \"DATE\" \n" + 
            "from\n" +
            "   parameters\n" + 
            "where\n" + 
            "   pers_id = :personId and\n" + 
            "   attr_id = :attrId\n" + 
            "order by\n" + 
            "   order_number";

		final Query query = em.createNativeQuery(sql);
		query.setParameter("personId", personId);
		query.setParameter("attrId", attrId);

		return attrValueFactory.createValue(personId, attrId,
				query.getResultList());
    }
    
    @SuppressWarnings("unchecked")
	@Override
    public <T> Collection<AttributeValue<T>> getParameters (Integer personId)
    {
        final String sql = 
            "select\n" + 
            "   list_value_id,\n" + 
            "   value,\n" + 
            "   \"DATE\" , \n" + 
            "   attr_id\n" + 
            "from\n" +
            "   parameters\n" + 
            "where\n" + 
            "   pers_id = :personId \n" + 
            "order by\n" + 
            "   attr_id, order_number";
        
        final Query query = em.createNativeQuery(sql);
        query.setParameter("personId", personId);
            
        return attrValueFactory.createValues(personId, query.getResultList());
    }
}